﻿-- drop function public.names(text, text, text, text, text);
create or replace function public.names(
  tname text, 
  sep text DEFAULT ', ', 
  quote text DEFAULT 'quote_literal', 
  orderby text DEFAULT 'ordinal_position', 
  schema text DEFAULT NULL) returns text AS 
$body$
/* 
Return the column names of table 'tname' as a single string conveniently quoted
and separated for use in other scripts.

Essentially names() queries *information_schema.columns* with the passed args 
and uses array_to_string(array_agg(), sep) to reformat the output.

Requires PostgreSQL 9.x or above as it uses named arguments.

ARGUMENTS

  tname <no default>:
     The table for which column names have to be returned. 
     This string passed to "...WHERE table_name = quote_literal(tname) ..."

  sep <', '>: 
     A string to separate the column names (passed to 
     array_to_string(column_array, sep))

  quote <'quote_literal'>: 
     Determines how column names should be quoted inside the returned string. 
     Options are:
       - 'quote_literal': Keyword to single-quote each name using function 
                          quote_literal()
       - 'quote_ident':   Keyword to double-quote names containg capital
                          letters or metacharacters using quote_ident()
       - any string:      This string will be concatenated exactly as is.
       
  orderby <'ordinal_position'>: 
     Order in which names shuld be returned. It should be a column name in 
     information_schema.columns as it is passed to the ORDER BY clause in 
     SELECT ... FROM information_schema.columns.
     Use 'ordinal_position' (defualt) for the order in the table or 
     'column_name' for alphanumeric order (ascending order only)
     
  schema <NULL>: 
     Schema where tname should be found. NULL (defualt) queries the entire
     information_schema.columns table.

EXAMPLES:

  select names('columns', schema := 'information_schema'); 
  >>> 'table_catalog', 'table_schema', 'table_name', ...
  
  select names('columns', schema := 'information_schema', sep := E'\n'); -- NOTE: E'' to enable interpretation of \n as newline
  >>> 'table_catalog'
      'table_schema'
      'table_name'
       ...
       
  select names('columns', schema := 'information_schema', quote := '"');
  >>> "table_catalog", "table_schema", "table_name", ...

  select names('columns', schema := 'information_schema', quote := 'quote_ident'); -- Convenient to copy and paste in a SELECT query
  >>> table_catalog, table_schema, table_name, ...

TODO:
  - Enable descending ordering of orderby.
*/
  DECLARE colnames text;
  DECLARE whereschema text;
  BEGIN

  IF     quote = 'quote_literal' THEN quote := 'quote_literal(column_name::text)'; 
  ELSEIF quote = 'quote_ident'   THEN quote := 'quote_ident(column_name::text)';
  ELSE   quote := quote_literal(quote) || '|| column_name::text ||' || quote_literal(quote);
  END IF;

  IF schema IS NULL THEN whereschema := '';
  ELSE whereschema := ' AND table_schema = ' || quote_literal(schema);
  END IF;
  
  DROP TABLE IF EXISTS tmp_namescols;
  EXECUTE 'CREATE TEMP TABLE tmp_namescols AS (
      SELECT ' || quote || ' AS q_column_name
      FROM information_schema.columns 
      WHERE table_name = ' || quote_literal(tname) || whereschema 
      || ' ORDER BY ' || quote_ident(orderby) || ')' ;
  
  SELECT array_to_string(array_agg(q_column_name), sep) INTO colnames FROM tmp_namescols; 

  RETURN colnames;
  END;
$body$
LANGUAGE 'plpgsql';